CREATE DATABASE ShradhaBookShop
USE ShradhaBookShop
--DROP DATABASE ShradhaBookShop
GO
CREATE TABLE Employees
(
	EmployeeID nchar(8) constraint pk_Employees primary key,
	UserName nvarchar(50) not null ,
	[Password] nvarchar(225) not null,
	FirstName nvarchar(100),
	LastName nvarchar(100),
	DateOfBirth datetime,
	Email nvarchar(100),
	Phone nvarchar(25)
)
GO
CREATE TABLE Customers
(
	CustomerID nchar(8) constraint pk_Customer primary key,
	CustomerName nvarchar(225),
	UserName nvarchar(50),
	[Password] nvarchar(225),
	Phone nvarchar(25),
	Email nvarchar(100),
	[Address] nvarchar(MAX),
	[Status] int constraint df_Customer_Status default (1)
)
GO
CREATE TABLE Authors
(
	AuthorID int identity(1,1) constraint pk_Author primary key,
	AuthorName nvarchar(100),
)
GO
CREATE TABLE Suppliers
(
	SupplierID nchar(8) constraint pk_Subpllier primary key,
	CompanyName nvarchar(40) not null,
	Phone nvarchar(25),
	Fax nvarchar(25),
	[Address] nvarchar(MAX)
)
GO
CREATE TABLE Categories
(
	CategoryID nchar(8) constraint pk_Categories primary key,
	CategoryName nvarchar(50) not null,
	Picture nvarchar(225)
)
GO
CREATE TABLE Brands
(
	BrandID int identity(1,1) constraint pk_Brands primary key,
	CategoryID nchar(8) constraint fk_Categories_Brands foreign key ([CategoryID]) references Categories([CategoryID]),
	BrandName nvarchar(50) not null
)
GO
CREATE TABLE Products
(
	ProductID nchar(7) constraint pk_Products primary key,
	ProductName nvarchar(100) not null,
	SupplierID nchar(8) constraint fk_Suppliers_Products foreign key ([SupplierID]) references Suppliers([SupplierID]),
	BrandID int constraint fk_Brands_Products foreign key ([BrandID]) references Brands([BrandID]),
	AuthorID int constraint fk_Authors_Products foreign key ([AuthorID]) references Authors([AuthorID]),
	QuantityPerUnit nvarchar(20) not null,
	UnitPrice money constraint df_Products_UnitPrice  DEFAULT (0) constraint ck_Products_UnitPrice check (([UnitPrice] >= 0)),
	UnitInStock int constraint df_Products_UnitInStock  DEFAULT (0) constraint ck_Products_UnitsInStock check  (([UnitInStock] >= 0)),
	UnitInOrder int constraint df_Products_UnitInOrder  DEFAULT (0) constraint ck_Products_UnitInOrder check  (([UnitInOrder] >= 0)),
	Hits int,
	RatingSum int constraint df_Products_RatingSum  DEFAULT (1),
	RatingCount int constraint df_Products_RatingCount  DEFAULT (1),
	Picture nvarchar(MAX)
)
GO
CREATE TABLE Orders
(
	OrderID nchar(8) constraint pk_Orders primary key,
	CustomerID nchar(8) constraint fk_Customers_Orders foreign key ([CustomerID]) references Customers([CustomerID]),
	EmployeeID nchar(8) constraint fk_Employees_Orders foreign key ([EmployeeID]) references Employees([EmployeeID]),
	OrderDate datetime,
	ShippedDate datetime,
	Freight money,
	[Status] nvarchar(50)
)
GO
CREATE TABLE OrderDetails
(
	OrderID nchar(8) constraint fk_OrderDetails_Orders foreign key ([OrderID]) references Orders([OrderID]),
	ProductID nchar(7) constraint fk_OrderDetails_Products foreign key ([ProductID]) references Products([ProductID]),
	UnitPrice money NOT NULL constraint df_OrderDetails_UnitPrice  DEFAULT (0),
	Quantity int NOT NULL constraint df_Order_Details_Quantity  DEFAULT (1),
	Discount real NOT NULL constraint df_Order_Details_Discount  DEFAULT (0),
	constraint [PK_Order_Details] primary key clustered 
	(
	OrderID ASC,
	ProductID ASC
	)
)
GO
CREATE TABLE CardDetails
(
	CardID int identity(1,1) constraint pk_CardDetails primary key,
	CardNo int,
	HolderName nvarchar(50),
	ExpiryDate nvarchar(10),
	Bank nvarchar(50),
	CardType nvarchar(50) 
)
GO
CREATE TABLE Payments
(
	PaymentID int identity(1,1) constraint pk_Payments primary key,
	OrderID nchar(8) constraint fk_Payments_Orders foreign key ([OrderID]) references Orders([OrderID]),
	CardID int constraint fk_CardDetails_Payments foreign key ([CardID]) references CardDetails([CardID]),
	PaymentType nvarchar(50),
	Amount money
)
GO
CREATE TRIGGER Tgr_Id_Order
ON Orders
INSTEAD OF INSERT
AS
BEGIN
DECLARE @id nchar(8),
@CustID nchar(8),
@EmpID nchar(8),
@Odate nvarchar(MAX),
@Sdate nvarchar(MAX),
@fr money,
@status nvarchar(50)
SELECT @id = MAX(RIGHT(OrderID,6)) FROM Orders
IF @id IS NULL
SELECT @id ='00000'
SELECT @id = LEFT('ODR00000',8-LEN(@id+1)) + CONVERT(NCHAR(8),@id+1)
SELECT @CustID = CustomerID,
@EmpID = EmployeeID,
@Odate = OrderDate,
@Sdate = ShippedDate,
@fr = Freight,
@status = [Status]
FROM INSERTED
INSERT Orders
VALUES (@id,@CustID,@EmpID,convert(datetime,@Odate,101),convert(datetime,@Sdate,101),@fr,@status)
END
GO
CREATE TRIGGER Tgr_Id_Employee
ON Employees
INSTEAD OF INSERT
AS
BEGIN
DECLARE @eid nchar(8),
@uname nvarchar(50),
@pass nvarchar(225),
@fn NVARCHAR(100),
@ln	 NVARCHAR(100),
@dob nvarchar(max),
@e	 nvarchar(100),
@p	 nvarchar(25)
SELECT @eid = MAX(RIGHT(EmployeeID,5)) FROM Employees
IF @eid IS NULL
SELECT @eid ='00000'
SELECT @eid = LEFT('EMP00000',8-LEN(@eid+1)) + CONVERT(NCHAR(8),@eid+1)
SELECT @uname = UserName,
@pass = [Password],
@fn = FirstName,
@ln = LastName,
@dob =DateOfBirth,
@e = Email,
@p = Phone
FROM INSERTED
INSERT Employees
VALUES (@eid,@uname,@pass,@fn,@ln,convert(datetime,@dob,101),@e,@p)
END
GO
CREATE TRIGGER Tgr_Id_Customer
ON Customers
INSTEAD OF INSERT
AS
BEGIN
DECLARE @id nchar(8),
@custName nvarchar(225),
@uname nvarchar(50),
@pass nvarchar(225),
@e	 nvarchar(100),
@p	 nvarchar(25),
@add nvarchar(max),
@status	 int
SELECT @id = MAX(RIGHT(CustomerID,5)) FROM Customers
IF @id IS NULL
SELECT @id ='00000'
SELECT @id = LEFT('CTM00000',8-LEN(@id+1)) + CONVERT(NCHAR(8),@id+1)
SELECT @custName = CustomerName,
@uname = UserName,
@pass = [Password],
@e = Email,
@p = Phone,
@add = [Address],
@status = [Status]
FROM INSERTED
INSERT Customers
VALUES (@id,@custName,@uname,@pass,@e,@p,@add,@status)
END
go
insert into authors values('Orther')
insert into authors values('Joanne Rowling')
insert into authors values('Kenneth Grahame')
insert into authors values('Graham Robb')
insert into authors values('Julian Barnes')
go 
insert into suppliers values('PHO','Phoenix Color Corp','(555) 443.3220','','Liverpool,England')
insert into suppliers values('MAL','Malloy Inc','(555) 553.3245','','Manchester,England')
insert into suppliers values('COU','Courier Corp','(555) 983.2345','','London,England')
insert into suppliers values('FRI','Friesens Corp','(555) 233.8791','','Newcastle,England')
go
insert into categories values('B1',N'Books','')
insert into categories values('M2',N'Magazine','')
insert into categories values('F3',N'Paper Files','')
insert into categories values('C4',N'CD s','')
insert into categories values('D5',N'DVD s','')
insert into categories values('U6',N'Utilities','')
go
insert into brands values('B1',N'AudioBooks')
insert into brands values('B1',N'Business & Money')
insert into brands values('B1',N'Computing & Internet')
insert into brands values('B1',N'Cookbooks, Food & Wine')
insert into brands values('B1',N'Diet & Health')
insert into brands values('B1',N'Entertainment')
insert into brands values('M2',N'Animals & Pets')
insert into brands values('M2',N'Art & Entertainment')
insert into brands values('M2',N'Business & Finance')
insert into brands values('M2',N'Computer')
insert into brands values('F3',N'Tag Files')
insert into brands values('F3',N'Printed Card Files')
insert into brands values('F3',N'Business & Finance')
insert into brands values('C4',N'Readable & Writable')
insert into brands values('C4',N'Readable')
insert into brands values('D5',N'Action Movies')
insert into brands values('D5',N'Animation')
insert into brands values('D5',N'Children')
insert into brands values('U6',N'Pen')
insert into brands values('U6',N'Pencil')
insert into brands values('U6',N'Paper')
insert into brands values('U6',N'Sketches')
go
insert into Customers(CustomerName,username,[password],phone,email,[Address]) values('Bui Huy Phong','bhphongqh','123456','+84989055454','phong225@gmail.com','Thai binh')
insert into Employees(username,[password],firstname,lastname,dateofbirth,email,phone) values('bhphongqh','123456','Huy','Phong','2/22/1986','phong225@gmail.com','+84989055454')
select * from employees
select * from Customers
select * from Orders
select * from OrderDetails
insert into Products values ('B1PHO01','The Art of Fair Isle Knitting','PHO','1','2','Q','10','20','15','10','40','5','image.jpg')
insert into Products values ('M2COU01','The Art of Fair Isle Knitting','COU','7','3','Q','30','20','15','10','25','5','image.jpg')
insert into Orders(CustomerID,EmployeeID,OrderDate,ShippedDate,Freight,[Status]) values ('CTM00001','EMP00001','2/23/2011','2/24/2011',10,'completed')
insert into OrderDetails values('ODR00001','B1PHO01',10,5,0)
insert into OrderDetails values('ODR00001','M2COU01',30,2,0)